Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# NLS Options

Oracle bases its globalization support on the values of parameters that begin with `NLS`. The Oracle OLAP DML includes a number options that correspond to these parameters.

Within a session, you can dynamically modify the value of some of these NLS parameters by setting them using the OLAP DML options described in Table 18-2, "OLAP DML NLS Options" or by using the SQL statement `ALTER SESSION` `SET` `option` `=` `value`.

Table 18-2 OLAP DML NLS Options

Option Name | Description
--- | ---
NLS_CALENDAR | The calendar for the session.
NLS_CURRENCY | The local currency symbol for the `L` number format element for the session. (See the TO_NUMBER function for a description of number format elements.)
NLS_DATE_FORMAT | The default format for `DATETIME` values.
NLS_DATE_LANGUAGE | The language for days, months, and similar language-dependent `DATE` format elements.
NLS_DUAL_CURRENCY | A second currency symbol that takes the place of the letter `U` in a number format mode and is used primarily to identify the Euro symbol. (Note that when you want to identify the Euro symbol as the value of NLS_DUAL_CURRENCY, the instance character set must support that symbol.)
NLS_ISO_CURRENCY | The international currency symbol for the `C` number format element.
NLS_LANG | The current language, territory, and database character set, which are determined by session-wide globalization parameters.
NLS_LANGUAGE | The current language for the session. The setting of this option determines the value of the SESSION_NLS_LANGUAGE option.
NLS_NUMERIC_CHARACTERS | The decimal marker and thousands group marker for the session. NLS_NUMERIC_CHARACTERS affects the display of numeric data and the setting of the OLAP DML THOUSANDSCHAR option, the DECIMALCHAR option, or both.
NLS_SORT | the sequence of character values used when sorting or comparing text. The value of NLS_SORT affects the `GT`, `GE`, `LT`, and `LE` operators, SORT command, and SORTLINES.
NLS_TERRITORY | The current territory for the session.

Data Type

TEXT

Syntax

option-name = option-value

Arguments

See Oracle Database SQL Reference for more information about NLS parameters, including valid values.

Examples

Example 18-21 Changing Calendar Systems

The following statement sets NLS_CALENDAR to the Thai Buddha calendar.

    NLS_CALENDAR = 'THAI BUDDHA'

Example 18-22 Setting the Language for Dates

The following statements set the language for dates to Spanish and change the default date format.

    NLS_DATE_LANGUAGE = 'SPANISH'
    NLS_DATE_FORMAT = 'Month DD, YYYY'

A `SHOW SYSDATE` statement now generates the date in Spanish.

    Septiembre 08, 2000

Example 18-23 Checking the Current Value

A `SHOW NLS_LANG` statement might produce the following.

    AMERICAN_AMERICA.WE8ISO8859P1

Example 18-24 Effects of Changing NLS_LANGUAGE

In this example, the NLS_LANG option is initially set to:

    AMERICAN_AMERICA.WE8ISO8859P1

The value of YESSPELL is `yes`.

A change to the language setting:

    NLS_LANGUAGE = 'FRENCH'

changes the value of NLS_LANG to

    FRENCH_AMERICAN.WE8ISO8859P1

The value of YESSPELL is now `oui`.

Example 18-25 Changing the Decimal Marker to a Comma

The following statement changes the decimal marker to a comma, and the thousands marker to a space.

    NLS_NUMERIC_CHARACTERS = ', '

The result of the following statement

    show 1234.56

is now

    1 234,56

Example 18-26 Binary and Linguistic Sorts

A dimension named `words` has the following values.

    cerveza, Colorado, cheremoya, llama, luna, lago

This example shows the results of a binary sort.

    NLS_SORT = 'BINARY'
    SORT words A words
    STATUS words
    The current status of WORDS is:
    Colorado, cerveza, cheremoya, lago, llama, luna

A Spanish language sort results in this order.

    NLS_SORT = 'SPANISH'
    SORT words A words
    STATUS words
    The current status of WORDS is:
    cerveza, cheremoya, Colorado, lago, llama, luna

An extended Spanish language sort results in this order.

    NLS_SORT = 'XSPANISH'
    SORT words A words
    STATUS words
    The current status of WORDS is:
    cerveza TO cheremoya, lago TO llama

Example 18-27 Effects of Changing NLS_TERRITORY

In this example, the NLS_LANG option is initially set to:

    AMERICAN_AMERICA.WE8ISO8859P1

The thousands marker is a comma (`,`), and the decimal marker is a period (`.`).

    SHOW TO_NUMBER('12345')
    12,345.00

A change to the territory setting:

    NLS_TERRITORY = 'FRANCE'

changes the value of NLS_LANG to

    AMERICAN_FRANCE.WE8ISO8859P1

The thousands marker is now a period (`.`), and the decimal marker is a comma (`,`).

    SHOW TO_NUMBER('12345')
    12.345,00